package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.LogFactory;

import com.seeyon.ctp.util.UUIDLong;

import fy.config.FyConfig;
import fy.uap.bean.ApptitudeBean;
import fy.uap.bean.ProviderImportFormBean;
import fy.uap.bean.RangeBean;
import fy.util.DBUtil;
import fy.util.MyUtil;


public class ProviderImportFormDao {
	// UPDATE_VENDOR_INVCLASS_STATE_CHANGE
	// insertVendorInvcalss
	// insertVendorInvcalssIfNotBlacklist
	private static final String UPDATE_VENDOR_INVCLASS_STATE_CHANGE = "update fy_supplier_vendor_invclass set cvendorclass=?, changeTrace=?, denddate=?,bankaccountname=?,bankcode = ?, bank = ?,bankaccount = ?,currency = ? where pk_veninv=? and cvendorclass<>'3'";

	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(ProviderImportFormDao.class);

	private final static String updateSuppliserBankInfo = "update fy_supplier_vendor_invclass i set i.bankaccountname=?,i.bankcode = ?, i.bank = ?,i.bankaccount = ?,i.currency = ? where i.pk_vendor = ( select v.pk_vendor from fy_supplier_vendor v where v.cvencode = ? and nvl(v.dr,0) = 0) and i.pk_corp=?";

	private final static String insertSuppliserVendor = "insert into fy_supplier_vendor (pk_vendor,cvencode,cvenname,ctrade,cvenregcode,dorf,cqualitycond,cvenbank,cvenaccount,cmoneytype,cpurbillcode,couttype,cvenaddress,cvenpostcode, cvenperson, cvenphone,cvenfax,cvenemail,cveniaddress,ccuspaycond,ccontent,cycle,ccreateperson,dvendevdate, ts, creater,cVenBankMemo,cvenfygl) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private final static String updateSuppliserVendor = "update fy_supplier_vendor set cvenname=?,ctrade=?,cvenregcode=?,dorf=?,cqualitycond=?,cvenbank=?,cvenaccount=?,cmoneytype=?,cpurbillcode=?,couttype=?,cvenaddress=?,cvenpostcode=?, cvenperson=?, cvenphone=?,cvenfax=?,cvenemail=?,cveniaddress=?,ccuspaycond=?,ccontent=?,cycle=?,ccreateperson=?,dvendevdate=?, ts=?,cVenBankMemo=?,cvenfygl=? where cvencode=? and (dr='0' or dr is null) ";

	private final static String insertVendorInvcalss = "insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate, changeTrace,creater,bankaccountname,bankcode, bank,bankaccount,currency) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?)";

	private static final String SELECT_VENDOR_INVCLASS = "select t.pk_vendor, trim(t.pk_corp), t.pk_invclass, t.cvendorclass, t.pk_veninv,t.denddate from fy_supplier_vendor_invclass t, fy_supplier_vendor v where (t.dr='0' or t.dr is null) and v.pk_vendor = t.pk_vendor and v.cvencode = ? ";

	private final static String insertVendorInvcalssIfNotBlacklist = "insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate, changeTrace, creater,bankaccountname,bankcode, bank,bankaccount,currency) select ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,? from dual where not exists ( select * from fy_supplier_vendor_invclass where pk_corp=? and pk_invclass=? and pk_vendor=? and (dr='0' or dr is null) and ( cvendorclass = '3' or cvendorclass = '0'))";

	private final static String insertLog = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate,creater) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

	private final static String insertLogIfNotBlacklist = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate, creater) select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual where not exists (select * from fy_supplier_vendor_invclass where pk_corp=? and pk_invclass=? and pk_vendor=? and (dr='0' or dr is null) and cvendorclass = '3')";

	private final static String uapUserID = "0001AA10000000009KX7";

//	private static final String ERPDELETESQL = "delete from apps.FYG_PO_UAP_VENDORS@"
//			+ FyConfig.getERP_DB_CONFIG().getDblink()
//			+ " i where i.VENDORS_NUM = ?  and i.ZT<>'客户' ";

	private static final String ERPUADATE = "update apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ " av set (av.vendors_name, av.flag,av.zt,av.disable_date) = (select v.cvenname cvenname,      decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),   -1,   'N',   decode(vi.cvendorclass, '3', 'N', 'Y')) flag   ,decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',   decode(vi.cvendorclass,          '1',          '合格',          '2',          '潜在',          '3',          '黑名单',          vi.cvendorclass)) zt,      to_date(vi.denddate, 'YYYY-MM-DD') disable_date from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp left join fy_supplier_invclass i on vi.pk_invclass =       i.pk_invclass          where v.cvencode = ? and (vi.dr is null or vi.dr = 0) and av.vendors_num =v.cvencode and av.syb=c.unitname and av.uaplb = i.cinvclasscode )  where av.vendors_num =  ?";

	private static final String ERPINSERTSQL = "insert into apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag) select * from (select v.cvencode cvencode,  v.cvenname cvenname,  c.unitname unitname,  i.cinvclasscode cinvclasscode,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',decode(vi.cvendorclass,'1','合格',  '2',  '潜在',  '3',  '黑名单',  vi.cvendorclass)) zt,  to_date(vi.denddate, 'YYYY-MM-DD') disable_date,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'N',decode(vi.cvendorclass,  '3',  'N',  'Y')) flag    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where v.cvencode=? and (vi.dr is null or vi.dr = 0) ) t where not exists ( select *    from apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ " e where t.cvencode = e.VENDORS_NUM and e.SYB = t.unitname and e.UAPLB=t.cinvclasscode and t.zt = e.zt and (t.disable_date is null and e.disable_date is null or t.disable_date = e.disable_date) ) ";

	private ProviderImportFormBean providerImportFormBean;

	private List<String> companyList;

	private List<RangeBean> failRangeList;

	private List<RangeBean> unchangingRangeList;

	private List<RangeBean> successRangeList;

	private boolean writeToErpSuccess = false;

//	private List<ApptitudeBean> apptitudeBeans;

	private boolean writeAptitudeSuccess;

//	public void setApptitudeBeans(List<ApptitudeBean> apptitudeBeans) {
//		this.apptitudeBeans = apptitudeBeans;
//	}

	public List<RangeBean> getFailRangeList() {
		return failRangeList;
	}

	public List<RangeBean> getUnchangingRangeList() {
		return unchangingRangeList;
	}

	public List<RangeBean> getSuccessRangeList() {
		return successRangeList;
	}

	public void setProviderImportFormBean(
			ProviderImportFormBean providerImportFormBean) {
		this.providerImportFormBean = providerImportFormBean;
	}

	public boolean save() {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;
		ResultSet rs = null;
		try {

			// FileOutputStream fos = new FileOutputStream("E:/1111");
			// fos.write(providerImportFormBean.printFields().getBytes());
			// fos.flush();
			// fos.close();

			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
			long changeTrace = UUIDLong.longUUID();
			// System.out.println(this.providerImportFormBean.printFields());

			// 新增加供应商的导入
			// OA 0001AA10000000009KX7
			// UUIDLong.longUUID()
			// 1001 单位
			// "insert into fy_supplier_vendor
			// (pk_vendor,cvencode,cvenname,ctrade,cvenregcode,
			// dorf,cqualitycond,cvenbank,cvenaccount,cmoneytype,
			// cpurbillcode,couttype,cvenaddress,cvenpostcode,
			// cvenperson, cvenphone,cvenfax,cvenemail,cveniaddress,
			// ccuspaycond,ccontent,cycle,ccreateperson,dvendevdate)
			// values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			conn.setAutoCommit(false);
			if ("1".equals(this.providerImportFormBean.getProviderIsExist())) {

				pst = conn.prepareStatement(insertSuppliserVendor);
				String pk_vendor = String
						.valueOf(UUIDLong
								.longUUID());

				pst.setString(1, pk_vendor);
				pst.setString(2, providerImportFormBean.getProviderCode());
				pst.setString(3, providerImportFormBean.getProviderName());
				pst.setString(4, providerImportFormBean.getCompanyType());
				pst.setString(5, providerImportFormBean.getTaxRegister());
				pst.setString(6, providerImportFormBean.getProperty());
				pst.setString(7, providerImportFormBean.getQualitySystem());
				pst.setString(8, providerImportFormBean.getCvenbank());
				pst.setString(9, providerImportFormBean.getBankAccount());
				pst.setString(10, providerImportFormBean.getCmoneytype());
				pst.setString(11, providerImportFormBean.getCpurbillcode());
				pst.setString(12, providerImportFormBean.getCouttype());
				pst.setString(13, providerImportFormBean.getSupplyAddress());
				pst.setString(14, providerImportFormBean.getPostCode());
				pst.setString(15, providerImportFormBean.getLinkman());
				pst.setString(16, providerImportFormBean.getTel());
				pst.setString(17, providerImportFormBean.getFax());
				pst.setString(18, providerImportFormBean.getEmail());
				pst.setString(19, providerImportFormBean.getAddress());
				pst.setString(20, providerImportFormBean.getCcuspaycond());
				pst.setString(21, providerImportFormBean.getCcontent());
				pst.setString(22, MyUtil.getLimitLengthString(
						providerImportFormBean.getSupplyCycle(), 20, "…"));
				pst.setString(23, uapUserID);
				pst.setString(24, MyUtil.getTodayDateStr());
				pst.setString(25, providerImportFormBean.getApplicationTime());
				pst.setString(26, providerImportFormBean.getMemberCode());
				pst.setString(27, providerImportFormBean.getcVenBankMemo());
				pst.setString(28, providerImportFormBean.getCvenfygl());

				// insert into fy_supplier_vendor
				// (pk_vendor,cvencode,cvenname,ctrade,cvenregcode,dorf,cqualitycond,cvenbank,cvenaccount,cmoneytype,cpurbillcode,couttype,cvenaddress,cvenpostcode,
				// cvenperson,
				// cvenphone,cvenfax,cvenemail,cveniaddress,ccuspaycond,ccontent,cycle,ccreateperson,dvendevdate,
				// ts,
				// creater,cVenBankMemo,cvenfygl,enterprise_type,registered_capital,establish_time,annual_sales,worker_number,premises,cvenlperson,legal_person_id_card
				// ) values
				// registered_capital VARCHAR2 (100) , --注册资金
				// establish_time VARCHAR2 (100) , --成立时间
				// annual_sales VARCHAR2 (100) , --年销售额
				// worker_number VARCHAR2 (100) , --职工总人数
				// premises VARCHAR2 (100) , --经营场所
				// legal_person_id_card VARCHAR2 (100) --法人代表身份证
//				pst.setString(29, providerImportFormBean.getQylb());
//				pst.setString(30, providerImportFormBean.getZczj());
//				pst.setString(31, providerImportFormBean.getClsj());
//				pst.setString(32, providerImportFormBean.getNxse());
//				pst.setString(33, providerImportFormBean.getZgrs());
//				pst.setString(34, providerImportFormBean.getJycs());
//				pst.setString(35, providerImportFormBean.getFrdb());
//				pst.setString(36, providerImportFormBean.getFrdbsfz());

				pst.executeUpdate();

				DBUtil.close(pst);

				// insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp,
				// pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate,
				// changeTrace) values ( ?, ?, ?, ?, ?, ?, ?, ?,
				// )
				pst = conn.prepareStatement(insertVendorInvcalss);

				// "insert into fy_supplier_vendor_history
				// (pk_venhis, pk_vendor, pk_corp, pk_invclass,
				// changetype, changememo, changevouch, changecode,
				// changer, changedate) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
				// )";
				pst1 = conn.prepareStatement(insertLog);

				for (int i = 0, size = this.companyList.size(); i < size; i++) {
					String uapAccountID = this.companyList.get(i);

					List<String> supplyRangeList = this.providerImportFormBean
							.getSupplyRangeList();
					for (int m = 0, mSize = supplyRangeList.size(); m < mSize; m++) {
						String supplyRange = supplyRangeList.get(m);
						String pk_veninv = String
								.valueOf(UUIDLong
										.longUUID());

						pst.setString(1, pk_veninv);
						pst.setString(2, uapAccountID);
						pst.setString(3, supplyRange);
						pst.setString(4, "2");
						pst.setString(5, this.providerImportFormBean
								.getApplicationTime());
						pst.setString(6, pk_vendor);
						pst.setString(7, MyUtil.getTodayDateStr());
						pst.setLong(8, changeTrace);
						pst.setString(9, providerImportFormBean.getMemberCode());

						// bankaccountname=?,bankcode = ?, bank =
						// ?,bankaccount = ?,currency = ?
						pst.setString(10,
								providerImportFormBean.getBankAccountName());
						pst.setString(11, providerImportFormBean.getBankCode());
						pst.setString(12,
								providerImportFormBean.getcVenBankMemo());
						pst.setString(13,
								providerImportFormBean.getBankAccount());
						pst.setString(14,
								providerImportFormBean.getCmoneytype());

						pst.addBatch();

						// "insert into fy_supplier_vendor_history
						// (pk_venhis, pk_vendor, pk_corp, pk_invclass,
						// changetype, changememo, changevouch, changecode,
						// changer, changedate) values ( ?, ?, ?, ?, ?, ?, ?, ?,
						// ?, ?
						// )";
						writeLog(pst1, pk_vendor, uapAccountID, supplyRange,
								"新增");
					}
				}

				pst.executeBatch();
				pst1.executeBatch();
				// delApptitude(conn);
				// insertApptitude(conn, pk_vendor);
				success = true;
			} else if ("0".equals(this.providerImportFormBean
					.getProviderIsExist())) {
				// 已导入供应商的更改
				pst = conn.prepareStatement(updateSuppliserVendor);
				
				pst.setString(1, providerImportFormBean.getProviderName());
				pst.setString(2, providerImportFormBean.getCompanyType());
				pst.setString(3, providerImportFormBean.getTaxRegister());
				pst.setString(4, providerImportFormBean.getProperty());
				pst.setString(5, providerImportFormBean.getQualitySystem());
				pst.setString(6, providerImportFormBean.getCvenbank());
				pst.setString(7, providerImportFormBean.getBankAccount());
				pst.setString(8, providerImportFormBean.getCmoneytype());
				pst.setString(9, providerImportFormBean.getCpurbillcode());
				pst.setString(10, providerImportFormBean.getCouttype());
				pst.setString(11, providerImportFormBean.getSupplyAddress());
				pst.setString(12, providerImportFormBean.getPostCode());
				pst.setString(13, providerImportFormBean.getLinkman());
				pst.setString(14, providerImportFormBean.getTel());
				pst.setString(15, providerImportFormBean.getFax());
				pst.setString(16, providerImportFormBean.getEmail());
				pst.setString(17, providerImportFormBean.getAddress());
				pst.setString(18, providerImportFormBean.getCcuspaycond());
				pst.setString(19, providerImportFormBean.getCcontent());
				pst.setString(20, MyUtil.getLimitLengthString(
						providerImportFormBean.getSupplyCycle(), 20, "…"));
				pst.setString(21, uapUserID);
				pst.setString(22, MyUtil.getTodayDateStr());
				pst.setString(23, providerImportFormBean.getApplicationTime());
				pst.setString(24, providerImportFormBean.getcVenBankMemo());
				pst.setString(25, providerImportFormBean.getCvenfygl());

//				pst.setString(26, providerImportFormBean.getQylb());
//				pst.setString(27, providerImportFormBean.getZczj());
//				pst.setString(28, providerImportFormBean.getClsj());
//				pst.setString(29, providerImportFormBean.getNxse());
//				pst.setString(30, providerImportFormBean.getZgrs());
//				pst.setString(31, providerImportFormBean.getJycs());
//				pst.setString(32, providerImportFormBean.getFrdb());
//				pst.setString(33, providerImportFormBean.getFrdbsfz());

				pst.setString(26, providerImportFormBean.getProviderCode());

				pst.executeUpdate();

				DBUtil.close(pst);

				pst = conn.prepareStatement(SELECT_VENDOR_INVCLASS);

				pst.setString(1, this.providerImportFormBean.getProviderCode());

				rs = pst.executeQuery();

				String pk_vendor = null;
				List<SupplyTypeBean> supplyTypeBeans = new ArrayList<SupplyTypeBean>();

				while (rs.next()) {
					if (pk_vendor == null) {
						pk_vendor = rs.getString(1);
					}
					SupplyTypeBean bean = new SupplyTypeBean();
					bean.setPk_corp(rs.getString(2));
					bean.setPk_invclass(rs.getString(3));
					bean.setCvendorclass(rs.getString(4));
					bean.setPk_veninv(rs.getString(5));
					bean.setDenddate(rs.getString(6));
					supplyTypeBeans.add(bean);
					bean = null;
				}
				DBUtil.close(rs, pst);

				if (pk_vendor == null) {
					pst = conn
							.prepareStatement("select v.pk_vendor from fy_supplier_vendor v where v.cvencode=?  and nvl(v.dr,0) = 0");
					pst.setString(1, providerImportFormBean.getProviderCode());
					rs = pst.executeQuery();
					while (rs.next()) {
						pk_vendor = rs.getString(1);
					}
				}
				DBUtil.close(rs, pst);
				// "insert into fy_supplier_vendor_invclass
				// (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts,
				// pk_vendor, dvendevdate,changeTrace ) values (?, ?, ?, ?, ?,
				// ?, ?, ?) where
				// pk_corp=? and pk_invclass=? and pk_vendor=? and cvendorclass
				// <> ?"
				pst = conn.prepareStatement(insertVendorInvcalssIfNotBlacklist);

				// "insert into fy_supplier_vendor_history
				// (pk_venhis, pk_vendor, pk_corp, pk_invclass,
				// changetype, changememo, changevouch, changecode,
				// changer, changedate) values ( ?, ?, ?, ?, ?,
				// ?, ?, ?, ?, ?
				// )where not exist (select * from where pk_corp=? and
				// pk_invclass=? and pk_vendor=? and cvendorclass = ?)";
				pst1 = conn.prepareStatement(insertLogIfNotBlacklist);

				pst2 = conn
						.prepareStatement(UPDATE_VENDOR_INVCLASS_STATE_CHANGE);

				String operate = "0".equals(this.providerImportFormBean
						.getOption()) ? "潜在" : "合格";
				String state = "0".equals(this.providerImportFormBean
						.getOption()) ? "2" : "1";

				for (int i = 0, size = this.companyList.size(); i < size; i++) {
					String uapAccountID = this.companyList.get(i);

					List<String> supplyRangeList = this.providerImportFormBean
							.getSupplyRangeList();

					for (int m = 0, mSize = supplyRangeList.size(); m < mSize; m++) {
						String supplyRange = supplyRangeList.get(m);
						SupplyTypeBean supplyTypeBean = null;
						if ((supplyTypeBean = typeIsExist(uapAccountID,
								supplyRange, supplyTypeBeans)) == null) {
							if ("0".equals(this.providerImportFormBean
									.getOption())) {
								String pk_veninv = String
										.valueOf(UUIDLong
												.longUUID());

								pst.setString(1, pk_veninv);
								pst.setString(2, uapAccountID);
								pst.setString(3, supplyRange);
								pst.setString(4, state);
								pst.setString(5, this.providerImportFormBean
										.getApplicationTime());
								pst.setString(6, pk_vendor);
								pst.setString(7, MyUtil.getTodayDateStr());
								pst.setLong(8, changeTrace);
								pst.setString(9,
										providerImportFormBean.getMemberCode());

								// bankaccountname=?,bankcode = ?, bank =
								// ?,bankaccount = ?,currency = ?
								pst.setString(10, providerImportFormBean
										.getBankAccountName());
								pst.setString(11,
										providerImportFormBean.getBankCode());
								pst.setString(12, providerImportFormBean
										.getcVenBankMemo());
								pst.setString(13,
										providerImportFormBean.getBankAccount());
								pst.setString(14,
										providerImportFormBean.getCmoneytype());

								// pk_corp=? and pk_invclass=? and pk_vendor=?
								// and cvendorclass
								// // <> ?"
								pst.setString(15, uapAccountID);
								pst.setString(16, supplyRange);
								pst.setString(17, pk_vendor);

								pst.addBatch();

								writeLogIfPrividerExist(pst1, pk_vendor,
										uapAccountID, supplyRange, operate);
							}

						} else {
							if (!"3".equals(supplyTypeBean.getCvendorclass())
									&& (!state.equals(supplyTypeBean
											.getCvendorclass())
											&& !"1".equals(supplyTypeBean
													.getCvendorclass()) || supplyTypeBean
											.getDenddate() != null
											&& !"".equals(supplyTypeBean
													.getDenddate())
											&& !MyUtil.later(supplyTypeBean
													.getDenddate(), "00:00:00",
													MyUtil.getTodayDateStr(),
													"00:00:00"))) {
								pst2.setString(1, state);
								pst2.setLong(2, changeTrace);
								pst2.setString(3, "");
								// bankaccountname=?,bankcode = ?, bank =
								// ?,bankaccount = ?,currency = ?
								pst2.setString(4, providerImportFormBean
										.getBankAccountName());
								pst2.setString(5,
										providerImportFormBean.getBankCode());
								pst2.setString(6, providerImportFormBean
										.getcVenBankMemo());
								pst2.setString(7,
										providerImportFormBean.getBankAccount());
								pst2.setString(8,
										providerImportFormBean.getCmoneytype());
								pst2.setString(9, supplyTypeBean.getPk_veninv());
								pst2.addBatch();

								writeLogIfPrividerExist(pst1, pk_vendor,
										uapAccountID, supplyRange, operate);
							} else {
								if (unchangingRangeList == null) {
									unchangingRangeList = new ArrayList<RangeBean>();
								}
								unchangingRangeList.add(new RangeBean(
										uapAccountID, supplyRange));
							}
						}
					}
				}
				pst.executeBatch();
				pst2.executeBatch();
				pst1.executeBatch();

			} else {
				log.error("无法确认供应商导入操作,供应商导入表单据编号："
						+ providerImportFormBean.getNumber());
				return false;
			}

			DBUtil.close(rs, pst);

			pst = conn.prepareStatement(updateSuppliserBankInfo);
			// update fy_supplier_vendor_invclass i set
			// i.bankaccountname=?,i.bankcode = ?, i.bank = ?,i.bankaccount =
			// ?,i.currency = ? where i.pk_vendor = ( select v.pk_vendor from
			// fy_supplier_vendor v where v.cvencode = ? and nvl(v.dr,0) = 0)
			for (int i = 0, size = this.companyList.size(); i < size; i++) {
				String uapAccountID = this.companyList.get(i);
				pst.setString(1, providerImportFormBean.getBankAccountName());
				pst.setString(2, providerImportFormBean.getBankCode());
				pst.setString(3, providerImportFormBean.getcVenBankMemo());
				pst.setString(4, providerImportFormBean.getBankAccount());
				pst.setString(5, providerImportFormBean.getCmoneytype());
				pst.setString(6, providerImportFormBean.getProviderCode());
				pst.setString(7, uapAccountID);
				pst.addBatch();
			}
			pst.executeBatch();
			DBUtil.close(pst);

			// ERP删除
//			pst = conn.prepareStatement(ERPDELETESQL);
//			pst.setString(1, providerImportFormBean.getProviderCode());
//			pst.execute();
			
			//ERP更新
			pst = conn.prepareStatement(ERPUADATE);
			pst.setString(1, providerImportFormBean.getProviderCode());
			pst.setString(2, providerImportFormBean.getProviderCode());
			pst.execute();
			
			// ERP插入
			pst = conn.prepareStatement(ERPINSERTSQL);
			pst.setString(1, providerImportFormBean.getProviderCode());
			pst.execute();
			conn.commit();
			DBUtil.setAutoCommit(conn, true);
			writeToErpSuccess = success = true;
			pst = conn
					.prepareStatement("select i.pk_corp, i.pk_invclass, c.unitname, si.cinvclasscode, v.cvencode, i.cvendorclass, v.cvenname from fy_supplier_vendor_invclass i left join bd_corp c on i.pk_corp = c.pk_corp left join fy_supplier_invclass si on si.pk_invclass = i.pk_invclass left join fy_supplier_vendor v on v.pk_vendor = i.pk_vendor where i.changeTrace= ?");
			pst.setLong(1, changeTrace);
			rs = pst.executeQuery();
			while (rs.next()) {
				// select i.pk_corp, i.pk_invclass, c.unitname,
				// si.cinvclasscode, v.cvencode, i.cvendorclass
				if (successRangeList == null) {
					successRangeList = new ArrayList<RangeBean>();
				}
				successRangeList.add(new RangeBean(rs.getString(1), rs
						.getString(2), rs.getString(3), rs.getString(5), rs
						.getString(4), rs.getString(6), rs.getString(7)));
			}
			setFailRangeList();

			writeAptitudeSuccess = true;
//					AptitudeDao.writeAptitude(conn,
//					apptitudeBeans, providerImportFormBean.getProviderCode(),
//					"供应商导入" + providerImportFormBean.getNumber(),
//					providerImportFormBean.getMemberCode());

		} catch (Exception e) {
			success = false;
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("供应商导入审批数据导入UPA异常", e);
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(pst2);
			DBUtil.close(pst1);
			DBUtil.close(rs, pst, conn);
		}

		// if (success)
		// writeToErp();

		return success;
	}

	public boolean isWriteAptitudeSuccess() {
		return writeAptitudeSuccess;
	}

	/**
	 * 写入到ERP数据库
	 */
	// private void writeToErp() {
	// if (successRangeList != null) {
	// Connection conn = null;
	// PreparedStatement pst = null;
	// try {
	//
	// // FileOutputStream fos = new FileOutputStream("E:/1111");
	// // fos.write(providerImportFormBean.printFields().getBytes());
	// // fos.flush();
	// // fos.close();
	//
	// conn = DBUtil.connect(ERP_OAConstant.ERPDriverClassName,
	// ERP_OAConstant.ERPURL, ERP_OAConstant.ERPUserName,
	// ERP_OAConstant.ERPPassword);
	//
	// DBUtil.setAutoCommit(conn, false);
	//
	// pst = conn
	// .prepareStatement("update apps.FYG_PO_UAP_VENDORS set ZT=? where VENDORS_NUM=? and SYB=? and UAPLB=?");
	// // update apps.FYG_PO_UAP_VENDORS set ZT=? where VENDORS_NUM=?
	// // and
	// // SY=? and UAPLB=?
	// RangeBean range = null;
	// for (int i = 0, size = successRangeList.size(); i < size; i++) {
	// range = successRangeList.get(i);
	// pst.setString(1, "2".equals(range.getCvendorclass()) ? "潜在"
	// : "合格");
	// pst.setString(2, range.getProviderCode());
	// pst.setString(3, range.getCorpName());
	// pst.setString(4, range.getInvclassCode());
	// pst.addBatch();
	// range = null;
	// }
	//
	// pst.executeBatch();
	// DBUtil.close(pst);
	// // insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp,
	// // pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate,
	// // changeTrace, creater) select ?, ?, ?, ?, ?, ?, ?, ?, ? from
	// // dual
	// // where not exists ( select * from fy_supplier_vendor_invclass
	// // where pk_corp=? and pk_invclass=? and pk_vendor=? and (dr='0'
	// // or
	// // dr is null) and ( cvendorclass = '3' or cvendorclass = '0'))
	// pst = conn
	// .prepareStatement("insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT) select  ?, ?, ?, ?, ? from dual where not exists  ( select * from apps.FYG_PO_UAP_VENDORS f where f.VENDORS_NUM=? and f.SYB=? and f.UAPLB=?)");
	// // insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM 供应商编号,
	// // VENDORS_NAME 供应商名称, SYB 事业部, UAPLB UAP类别, ZT 状态) select ?, ?,
	// // ?,
	// // ?, ?, ?, ?, ?, ? from dual where not exists ( select * from
	// // apps.FYG_PO_UAP_VENDORS f where f.VENDORS_NUM=? and f.SY=?
	// // and
	// // f.UAPLB=?)");
	//
	// for (int i = 0, size = successRangeList.size(); i < size; i++) {
	// range = successRangeList.get(i);
	//
	// pst.setString(1, range.getProviderCode());
	// pst.setString(2, range.getProviderName());
	// pst.setString(3, range.getCorpName());
	// pst.setString(4, range.getInvclassCode());
	// pst.setString(5, "2".equals(range.getCvendorclass()) ? "潜在"
	// : "合格");
	// pst.setString(6, range.getProviderCode());
	// pst.setString(7, range.getCorpName());
	// pst.setString(8, range.getInvclassCode());
	// pst.addBatch();
	// range = null;
	// }
	// pst.executeBatch();
	//
	// conn.commit();
	//
	// writeToErpSuccess = true;
	//
	// } catch (Exception e) {
	// DBUtil.rollback(conn);
	// DBUtil.setAutoCommit(conn, true);
	// e.printStackTrace();
	// log.error(
	// "供应商导入写入ERP数据库异常,单据编号： "
	// + providerImportFormBean.getNumber(), e);
	// writeToErpSuccess = false;
	// } finally {
	// DBUtil.setAutoCommit(conn, true);
	// DBUtil.close(pst, conn);
	// }
	// } else {
	// log.warn("供应商导入写入ERP数据库数据为空,单据编号： "
	// + providerImportFormBean.getNumber());
	//
	// }
	// }

	private void setFailRangeList() {
		if (successRangeList == null
				|| successRangeList.size() < this.companyList.size()
						* this.providerImportFormBean.getSupplyRangeList()
								.size()) {
			for (int i = 0, size = this.companyList.size(); i < size; i++) {
				String uapAccountID = this.companyList.get(i);
				List<String> supplyRangeList = this.providerImportFormBean
						.getSupplyRangeList();
				for (int m = 0, mSize = supplyRangeList.size(); m < mSize; m++) {

					String supplyRange = supplyRangeList.get(m);

					boolean fail = true;

					if (successRangeList != null) {
						for (int o = 0, sSize = successRangeList.size(); o < sSize; o++) {
							RangeBean bean = successRangeList.get(o);
							if (uapAccountID.equals(bean.getPk_corp())
									&& supplyRange
											.equals(bean.getPk_invclass())) {
								fail = false;
								break;
							}
							bean = null;
						}
					}
					if (fail && unchangingRangeList != null) {
						for (int o = 0, sSize = unchangingRangeList.size(); o < sSize; o++) {
							RangeBean bean = unchangingRangeList.get(o);
							if (uapAccountID.equals(bean.getPk_corp())
									&& supplyRange
											.equals(bean.getPk_invclass())) {
								fail = false;
								break;
							}
							bean = null;
						}
					}

					if (fail) {
						if (failRangeList == null) {
							failRangeList = new ArrayList<RangeBean>();
						}
						failRangeList.add(new RangeBean(uapAccountID,
								supplyRange));
					}
				}
			}
		}
	}

	private void writeLog(PreparedStatement pst1, String pk_vendor,
			String uapAccountID, String supplyRange, String operate)
			throws SQLException {
		pst1.setString(1,
				String.valueOf(UUIDLong.longUUID()));
		pst1.setString(2, pk_vendor);
		pst1.setString(3, uapAccountID);
		pst1.setString(4, supplyRange);
		pst1.setString(5, operate);
		pst1.setString(6, this.providerImportFormBean.getReason());
		pst1.setString(7, "OA供应商导入批准表");
		pst1.setString(8, this.providerImportFormBean.getNumber());
		pst1.setString(9, uapUserID);
		pst1.setString(10, MyUtil.getNowStr());
		pst1.setString(11, MyUtil.getTodayDateStr());
		pst1.setString(12, this.providerImportFormBean.getMemberCode());
		pst1.addBatch();
	}

	private void writeLogIfPrividerExist(PreparedStatement pst1,
			String pk_vendor, String uapAccountID, String supplyRange,
			String operate) throws SQLException {
		// insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp,
		// pk_invclass, changetype, changememo, changevouch, changecode,
		// changer, ts, changedate) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
		// where not exist (select * from where pk_corp=? and pk_invclass=? and
		// pk_vendor=? and cvendorclass <> '3')

		pst1.setString(1,
				String.valueOf(UUIDLong.longUUID()));
		pst1.setString(2, pk_vendor);
		pst1.setString(3, uapAccountID);
		pst1.setString(4, supplyRange);
		pst1.setString(5, operate);
		pst1.setString(6, this.providerImportFormBean.getReason());
		pst1.setString(7, "OA供应商导入批准表");
		pst1.setString(8, this.providerImportFormBean.getNumber());
		pst1.setString(9, uapUserID);
		pst1.setString(10, MyUtil.getNowStr());
		pst1.setString(11, MyUtil.getTodayDateStr());
		pst1.setString(12, providerImportFormBean.getMemberCode());
		pst1.setString(13, uapAccountID);
		pst1.setString(14, supplyRange);
		pst1.setString(15, pk_vendor);
		pst1.addBatch();
	}

	public boolean setUapAccountList() {
		this.companyList = this.providerImportFormBean.getCompanyIDList();
		return this.companyList != null && this.companyList.size() > 0;
	}

	private SupplyTypeBean typeIsExist(String pk_corp, String pk_invclass,
			List<SupplyTypeBean> supplyTypeBeans) {
		if (supplyTypeBeans != null) {
			for (int i = 0, size = supplyTypeBeans.size(); i < size; i++) {
				if (supplyTypeBeans.get(i) != null) {
					if (pk_corp.equals(supplyTypeBeans.get(i).getPk_corp())
							&& pk_invclass.equals(supplyTypeBeans.get(i)
									.getPk_invclass())) {
						return supplyTypeBeans.get(i);
					}
				}
			}
		}
		return null;
	}

	public boolean isWriteToErpSuccess() {
		return writeToErpSuccess;
	}

}

class SupplyTypeBean {
	private String pk_corp;
	private String pk_invclass;
	private String cvendorclass;
	private String pk_veninv;
	private String denddate;

	public String getDenddate() {
		return denddate;
	}

	public void setDenddate(String denddate) {
		this.denddate = denddate;
	}

	public String getPk_veninv() {
		return pk_veninv;
	}

	public void setPk_veninv(String pk_veninv) {
		this.pk_veninv = pk_veninv;
	}

	public String getPk_corp() {
		return pk_corp;
	}

	public void setPk_corp(String pk_corp) {
		this.pk_corp = pk_corp;
	}

	public String getPk_invclass() {
		return pk_invclass;
	}

	public void setPk_invclass(String pk_invclass) {
		this.pk_invclass = pk_invclass;
	}

	public void setCvendorclass(String cvendorclass) {
		this.cvendorclass = cvendorclass;
	}

	public String getCvendorclass() {
		return cvendorclass;
	}

}
